package com.example.demo.dao;

import com.example.demo.entity.UserVo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
  //根据参数userID查询User，将结果封装成UserVo返回
  //形参:
  //userID
  //返回值:
  //
  //抛出:
  //Exception

  public UserVo findByUserId(int userID) throws Exception{
    UserVo userVo=null;
    Connection con= null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    try {
      Class.forName(DB.JDBC_DRIVER);
      con= DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
      ps=con.prepareStatement("select * from User where UserID=? ");//UserID与数据库内的UserID对应，要一致
      ps.setInt(1,userID);//userID是变量名
      rs=ps.executeQuery();
      if (rs.next()){
        userVo=new UserVo();
        userVo.setUserID(rs.getInt("UserID"));//UserID与数据库内的UserID对应，要一致
        userVo.setUserName(rs.getString("Username"));
        userVo.setPassword(rs.getString("Password"));
        userVo.setTelephone(rs.getString("Telephone"));
        userVo.setGender(rs.getString("Gender"));
      }
    }
    catch (Exception e) {
      e.printStackTrace();
      throw new Exception("数据库异常:"+e.getMessage());
    }finally {
      if(rs!=null) rs.close();
      if(ps!=null) ps.close();
      if(con!=null) con.close();
    }
    return userVo;
  }
  //根据参数username模糊查询表User，将结果封装成List返回
  //形参:
  //username
  //返回值:
  //
  //抛出:
  //Exception
  public List<UserVo> findUserByName(String username) throws Exception {
    List<UserVo> list=new ArrayList<>();
    Connection con= null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    try {
      Class.forName(DB.JDBC_DRIVER);
      con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
      ps=con.prepareStatement("select * from User where Username like ? ");
      ps.setString(1,"%"+username+"%");
      rs=ps.executeQuery();
      while (rs.next()){
        UserVo userVo=new UserVo();
        userVo.setUserID(rs.getInt("UserID"));//UserID与数据库内的UserID对应，要一致
        userVo.setUserName(rs.getString("Username"));
        userVo.setPassword(rs.getString("Password"));
        userVo.setTelephone(rs.getString("Telephone"));
        userVo.setGender(rs.getString("Gender"));
        list.add(userVo);
      }
    } catch (Exception e) {
      e.printStackTrace();
      throw new Exception("数据库异常:"+e.getMessage());
    }finally {
      if(rs!=null) rs.close();
      if(ps!=null) ps.close();
      if(con!=null) con.close();
    }
    return list;
  }
   //添加数据
  public void saveUser(UserVo userVo) throws Exception {
    Connection con= null;
    PreparedStatement ps=null;
    try {
      Class.forName(DB.JDBC_DRIVER);
      con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
      ps=con.prepareStatement("insert into User (UserName,Password,Telephone,Gender) value (?,?,?,?)");

      ps.setString(1,userVo.getUserName());
      ps.setString(2,userVo.getPassword());
      ps.setString(3,userVo.getTelephone());
      ps.setString(4,userVo.getGender());

      ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
      throw new Exception("数据库异常:"+e.getMessage());
    }finally {
      if(ps!=null) ps.close();
      if(con!=null) con.close();
    }
  }
  //修改数据
  public void updateUser(UserVo userVo) throws Exception {
    Connection con= null;
    PreparedStatement ps=null;
    try {
      Class.forName(DB.JDBC_DRIVER);
      con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
        ps=con.prepareStatement("update User set Username=?,Password=? ,Telephone=?,Gender=? where UserID=?");
      ps.setString(1,userVo.getUserName());
      ps.setString(2,userVo.getPassword());
      ps.setString(3,userVo.getTelephone());
      ps.setString(4,userVo.getGender());
      ps.setInt(5,userVo.getUserID());
      ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
      throw new Exception("数据库异常:"+e.getMessage());
    }finally {
      if(ps!=null) ps.close();
      if(con!=null) con.close();
    }
  }
  //根据userid删除User表中对应的记录
  //形参:
  //userid
  //抛出:
  //Exception
  public void deleteUserById(int userID) throws Exception {
    Connection con= null;
    PreparedStatement ps=null;
    try {
      Class.forName(DB.JDBC_DRIVER);
      con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
      ps=con.prepareStatement("delete  from User where  UserID=?");
      ps.setInt(1,userID);
      ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
      throw new Exception("数据库异常:"+e.getMessage());
    }finally {
      if(ps!=null) ps.close();
      if(con!=null) con.close();
    }
  }

  public UserVo findByName(String username) throws Exception{
    UserVo userVo=null;
    Connection con= null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    try {
      Class.forName(DB.JDBC_DRIVER);
      con= DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
      ps=con.prepareStatement("select * from User where Username=? ");//UserID与数据库内的UserID对应，要一致
      ps.setString(1,username);//userID是变量名
      rs=ps.executeQuery();
      if (rs.next()){
        userVo=new UserVo();
        userVo.setUserID(rs.getInt("UserID"));//UserID与数据库内的UserID对应，要一致
        userVo.setUserName(rs.getString("Username"));
        userVo.setPassword(rs.getString("Password"));
        userVo.setTelephone(rs.getString("Telephone"));
        userVo.setGender(rs.getString("Gender"));
      }
    }
    catch (Exception e) {
      e.printStackTrace();
      throw new Exception("数据库异常:"+e.getMessage());
    }finally {
      if(rs!=null) rs.close();
      if(ps!=null) ps.close();
      if(con!=null) con.close();
    }
    return userVo;
  }
}
